GE实习之字符串模糊匹配---VBA

发表于 | 分类于 ，

直到 90 年代早期,使应用程序自动化还是充满挑战性的领域.对每个需要自动化的应用程序,人们不得不学习一种不同的自动化语言.例如:可以用EXCEL的宏语言来使EXCEL自动化,使用WORD BASIC使WORD自动化,等等.微软决定让它开发出来的应用程序共享一种通用的自动化语言——–Visual Basic For Application(VBA,可以认为 VBA 是非常流行的应用程序开发语言VASUAL BASIC 的子集.实际上VBA是”寄生于”VB应用程序的版本.

VBA

  • VBA功能
    1.使重复的任务自动化.
    2.自定义EXCEL工具栏,菜单和界面.
    3.简化模板的使用.
    4.自定义EXCEL,使其成为开发平台.
    5.创建报表.
    6.对数据进行复杂的操作和分析.

  • EXCEL作为开发平台的优点
    1.EXCEL本身功能强大,包括打印,文件处理,格式化和文本编辑.
    2.EXCEL内置大量函数.
    3.EXCEL界面熟悉.
    4.可连接到多种数据库.

VBA语言的基础语法

  • 标识符
    1.定义：标识符是一种标识变量、常量、过程、函数、类等语言构成单位的符号，利用它可以完成对变量、常量、过程、函数、类等引用。
    2.命名规则：
    (1)字母打头，由字母、数字和下划线组成，如A987b_23Abc
    (2)字符长度小于40
    (3)不能与VB保留字重名，如public，private，dim，goto，next，with，integer，single等

  • 运算符
    1.定义：运算符是代表VB某种运算功能的符号。
    2.运算符介绍：
    (1)赋值运算符 =
    (2)数学运算符 &、+ (字符连接符)、+(加)、-（减）、Mod（取余）、\（整除）、*（乘）、/
    （除）、-（负号）、^（指数）
    (3)逻辑运算符Not（非）、And（与）、Or(或)、Xor(异或)、Eqv(相等)、Imp(隐含)
    (4)关系运算符 = （相同）、<>（不等）、>（大于）、<（小于）、>=（不小于）、<=（不大于）、
    Like、Is
    (5)位运算符 Not（逻辑非）、And（逻辑与）、Or（逻辑或）、Xor（逻辑异或）、Eqv（逻辑
    等）、Imp（隐含）

  • 数据类型
    VBA共有12种数据类型，具体见下表，此外用户还可以根据以下类型用Type自定义数据类型。
    |数据类型|类型标识符|字节|
    |-|-|-|
    |字符串型 String|$|字符长度(0-65400)|
    |字节型 Byte|无|1|
    |布尔型 Boolean|无|2|
    |整数型 Integer|%|2|
    |长整数型 Long|&|4|
    |单精度型 Single|!|4|
    |双精度型 Double|#|8|
    |日期型 Date|无|8|
    |货币型 Currency|@|8|
    |小数点型 Decimal|无|14|
    |变体型 Variant|无|以上任意类型，可变|
    |对象型 Object|无|4|

  • 变量
    Dim 变量 as 类型 ‘定义为局部变量，如 Dim xyz as integer
    Private 变量 as 类型 ‘定义为私有变量，如 Private xyz as byte
    Public 变量 as 类型 ‘定义为公有变量，如 Public xyz as single
    Global 变量 as 类型 ‘定义为全局变量，如 Global xyz as date
    Static 变量 as 类型 ‘定义为静态变量，如 Static xyz as double

  • 注释和赋值语句
    1.注释语句是用来说明程序中某些语句的功能和作用,语句前加单引号’标识为注释语句。
    2.赋值语句是进行对变量或对象属性赋值的语句，采用赋值号 =。

  • 书写规范
    1.VBA不区分标识符的字母大小写，一律认为是小写字母
    2.一行可以书写多条语句，各语句之间以冒号 : 分开
    3.一条语句可以多行书写，以空格加下划线 _ 来标识下行为续行

  • 判断语句
    If…Then…Else语句：

    1
    2
    3
    4
    5
    If Number < 10  Then
        Digits = 1
    Else
    	Digits = 3
    End If

  • 循环语句
    For Next语句, 以指定次数来重复执行一组语句:

    1
    2
    3
    4
    5
    6
    For Words = 10 To 1 Step  -1            ＇建立 10 次循环 
    	For Chars = 0 To 9                  ＇建立 10 次循环
    		MyString = MyString & Chars     ＇将数字添加到字符串中
    	Next Chars                          ＇Increment counter
    	MyString = MyString & " "           ＇添加一个空格
    Next  Words

  • 过程和函数
    过程是构成程序的一个模块，往往用来完成一个相对独立的功能。过程可以使程序更清晰、更具结构性。VBA具有四种过程：Sub 过程、Function函数、Property属性过程和Event事件过程。

Sub 过程的参数有两种传递方式：按值传递(ByVal)和按地址传递(ByRef):

1
2
3
4
Sub password (ByVal x as integer, ByRef y as integer)
	If y=100 then y=x+y else y=x-y
	x=x+100
End sub

函数实际是实现一种映射，它通过一定的映射规则，完成运算并返回结果。参数传递也两种：按值传递(ByVal)和按地址传递(ByRef):

1
2
3
4
5
Function password(ByVal x as integer, byref y as integer) as boolean
	If y=100 then y=x+y else y=x-y
	x=x+100
	if y=150 then password=true else password=false
End Function

  • 内部函数
    在VBA程序语言中有许多内置函数，可以帮助程序代码设计和减少代码的编写工作。

测试函数:
IsNumeric(x) ‘是否为数字, 返回Boolean结果，True or False
IsDate(x) ‘是否是日期, 返回Boolean结果，True or False
IsEmpty（x） ‘是否为Empty, 返回Boolean结果，True or False
IsArray(x) ‘指出变量是否为一个数组。
IsError(expression) ‘指出表达式是否为一个错误值
IsNull(expression) ‘指出表达式是否不包含任何有效数据 (Null)。
IsObject(identifier) ‘指出标识符是否表示对象变量

字符串函数:
Trim(string) ‘去掉string左右两端空白
Ltrim(string) ‘去掉string左端空白
Rtrim(string) ‘去掉string右端空白
Len(string) ‘计算string长度
Left(string, x) ‘取string左段x个字符组成的字符串
Right(string, x) ‘取string右段x个字符组成的字符串
Mid(string, start,x) ‘取string从start位开始的x个字符组成的字符串
Ucase(string) ‘转换为大写
Lcase(string) ‘转换为小写
Space(x) ‘返回x个空白的字符串
Asc(string) ‘返回一个 integer，代表字符串中首字母的字符代码
Chr(charcode) ‘返回 string,其中包含有与指定的字符代码相关的字符

转换函数:
CBool(expression) ‘转换为Boolean型
CByte(expression) ‘换为Byte型
CCur(expression) ‘转换为Currency型
CDate(expression) ‘转换为Date型
CDbl(expression) ‘转换为Double型
CDec(expression) ‘转换为Decemal型
CInt(expression) ‘转换为Integer型
CLng(expression) ‘转换为Long型
CSng(expression) ‘转换为Single型
CStr(expression) ‘转换为String型
CVar(expression) ‘转换为Variant型
Val(string) ‘转换为数据型
Str(number) ‘转换为String

Levenshtein距离

  • 定义

编辑距离（Edit Distance），又称Levenshtein距离，是指两个字串之间，由一个转成另一个所需的最少编辑操作次数。许可的编辑操作包括将一个字符替换成另一个字符，插入一个字符，删除一个字符。一般来说，编辑距离越小，两个串的相似度越大。

  • 求解算法

比如要计算cafe->coffee的编辑距离。

c o f f e e
0 1 2 3 4 5 6
c 1 0 1 2 3 4 5
a 2 1 1 2 3 4 5
f 3 2 2 1 2 3 4
e 4 3 3 2 2 2 3

取右下角，得编辑距离为3。

  • 实现代码

功能：VBA对比计算两个字符串的相似度。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
Private Function min(one As Integer, two As Integer, three As Integer)
	min = one
    If (two < min) Then
		min = two
    End If
    If (three < min) Then
		min = three
    End If
End Function

'计算levenshtein距离 
Private Function ld(str1 As String, str2 As String)
'去掉字符串两端空格
str1 = RTrim(str1)
str2 = RTrim(str2)
Dim n, m, i, j As Integer
Dim ch1, ch2 As String
    n = Len(str1)    '存储字符串长度
    m = Len(str2)
    Dim temp As Integer
    If (n = 0) Then  '当第一个字符串长度为0
        ld = m       '返回第二个字符串长度
    End If
    If (m = 0) Then
        ld = n
    End If
Dim d As Variant
ReDim d(n + 1, m + 1) As Variant     '二维数组重定义
	'初始化数据
    For i = 0 To n
        d(i, 0) = i
    Next i
    For j = 0 To m
        d(0, j) = j
    Next j
    '正式计算levenshtein距离--算法核心
    For i = 1 To n
        ch1 = Mid(str1, i, 1)
        For j = 1 To m
            ch2 = Mid(str2, j, 1)
            If (ch1 = ch2) Then
            	temp = 0
            Else
                temp = 1
            End If
            d(i, j) = min(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + temp)
        Next j
    Next i
    '返回右下角值(即levenshtein距离 )
    ld = d(n, m)
End Function

'计算相似度 
Public Function sim(str1 As String, str2 As String)
    Dim ldint As Integer
    ldint = ld(str1, str2)
    Dim strlen As Integer
    If (Len(str1) >= Len(str2)) Then
        strlen = Len(str1)
    Else
        strlen = Len(str2)
    End If
    sim = 1 - ldint / strlen
End Function